import pymysql


class XpcMysqlPipeline:
    def __init__(self, host, port, db, user, password):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = password

    @classmethod
    def from_crawler(cls, crawler):
        mysql_config = crawler.settings.get('MYSQL_CONFIG')
        return cls(
            host=mysql_config['host'],
            port=mysql_config['port'],
            db=mysql_config['dbname'],
            user=mysql_config['user'],
            password=mysql_config['password']
        )

    def open_spider(self, spider):
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            db=self.db,
            user=self.user,
            password=self.password
        )
        self.cur = self.conn.cursor()

    def close_spider(self, spider):
        self.cur.close()
        self.conn.close()

    def process_item(self, item, spider):
        keys = item.keys()
        values = list(item.values())
        sql = 'insert into video_show({}) values ({}) '.format(
            ','.join(keys),
            ','.join(['%s'] * len(values))
        )

        # sql = 'insert into blog(title, publish, approval, unlike, `comment`, collection) values (%s, %s, %s, %s,
        # %s, %s)'
        # sql_exe = sql % tuple(val for val in values)
        # print(sql_exe, '='*10)
        self.cur.execute(sql, values)
        self.conn.commit()
        # print(self.cur._last_executed)
        return item
